import logging
from datetime import datetime
from typing import Optional
from fastapi import APIRouter, Depends, Query, Path
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

data_holiday_router = APIRouter(prefix="/data/holiday", tags=["节假日管理"], dependencies=[Depends(oauth2_scheme)])


class HolidaySearch(PaginationBase):
    fholidayname: str = Field(None, description="节假日名")
    begdate: datetime = Field(None, description="开始时间")
    enddate: datetime = Field(None, description="结束时间")


@data_holiday_router.get("/list", summary="节假日查询接口")
def list_holiday(holiday: HolidaySearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from a_ts_holiday where 1=1"
    if holiday.fholidayname is not None:
        sql = sql + " and fholidayname like concat('%', :holidayname ,'%')"
        param["holidayname"] = holiday.fholidayname
    if holiday.begdate is not None:
        sql = sql + " and fbegdate >= :begdate "
        param["begdate"] = holiday.begdate
    if holiday.enddate is not None:
        sql = sql + " and fenddate >= :enddate "
        param["enddate"] = holiday.enddate
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fholidayid,fsite_no,fholidayname,fbegdate,fenddate,foperator,fupddate,ftype"
    sql = sql + " from a_ts_holiday where 1=1"
    if holiday.fholidayname is not None:
        sql = sql + " and fholidayname like concat('%', :holidayname ,'%')"
        param["holidayname"] = holiday.fholidayname
    if holiday.begdate is not None:
        sql = sql + " and fbegdate >= :begdate "
        param["begdate"] = holiday.begdate
    if holiday.enddate is not None:
        sql = sql + " and fenddate >= :enddate "
        param["enddate"] = holiday.enddate
    sql = sql + " limit :limit offset :offset "
    param["offset"] = holiday.offset
    param["limit"] = holiday.limit
    data = db.execute(text(sql), param).fetchall()
    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fholidayid'] = data[key][0]
        d1['fsiteNo'] = data[key][1]
        d1['fholidayname'] = data[key][2]
        d1['fbegdate'] = data[key][3]
        d1['fenddate'] = data[key][4]
        d1['foperator'] = data[key][5]
        d1['fupddate'] = data[key][6]
        d1['ftype'] = data[key][7]
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": holiday.limit, "page": holiday.pageIndex,
                 "pages": (count + holiday.limit - 1) // holiday.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class HolidayCreate(BaseModel):
    """节假日资料新增请求参数"""
    fbegdate: str = Field(..., description="假期开始日期(yyyy-MM-dd)")
    fenddate: str = Field(..., description="假期结束日期(yyyy-MM-dd)")
    fholidayname: str = Field(..., description="假期名称")
    fsiteNo: str = Field(..., description="站点编号")
    ftype: int = Field(..., description="假期类型编码")
    fupddate: str = Field(..., description="更新日期(yyyy-MM-dd HH:mm:ss)")
    fholidayid: int = Field(None, description="节假日id")


@data_holiday_router.post("/create", summary="节假日资料新增接口")
def create_holiday(holiday: HolidayCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "select fholidayid from a_ts_holiday "
            sql = sql + " where 1=1"
            sql = sql + " and ((fbegdate between :fbegdate and :fenddate )"
            sql = sql + " or(fenddate between :fbegdate and :fenddate ))"
            data = db.execute(text(sql), {
                "fbegdate": holiday.fbegdate,
                "fenddate": holiday.fenddate
            }).fetchall()

            if len(data) > 0:
                return BaseResponse.error(message="该日期已设定假期")
            else:
                # 先取单号
                fholidayid = "0"
                sql = "select nextval('a_ts_holiday_fholidayid_seq') as last_value "
                res = db.execute(text(sql)).scalar()
                fholidayid = res[0][0]

                sql = "insert into a_ts_holiday(fholidayid,fsite_no,fholidayname,fbegdate,fenddate,foperator,fupddate,ftype)"
                sql = sql + " values( :fholidayid , :fsiteNo , :fholidayname , :fbegdate , :fenddate , :userId ,now(), :ftype );"
                db.execute(text(sql), {
                    "fholidayid": fholidayid,
                    "fsiteNo": holiday.fsiteNo,
                    "fholidayname": holiday.fholidayname,
                    "fbegdate": holiday.fbegdate,
                    "fenddate": holiday.fenddate,
                    "userId": user_id,
                    "ftype": holiday.ftype,
                })
                # 返回
                d1 = {}
                d1['fholidayid'] = fholidayid
                d1['fsiteNo'] = holiday.fsiteNo
                d1['fholidayname'] = holiday.fholidayname
                d1['fbegdate'] = holiday.fbegdate
                d1['fenddate'] = holiday.fenddate
                d1['foperator'] = user_id
                d1['fupddate'] = holiday.fupddate
                d1['ftype'] = holiday.ftype
                return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@data_holiday_router.post("/update", summary="节假日修改接口")
def update_holiday(holiday: HolidayCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "update a_ts_holiday"
            sql = sql + " set fholidayname= :fholidayname ,fbegdate= :fbegdate "
            sql = sql + ",fenddate= :fenddate ,ftype= :ftype "
            sql = sql + ",fupddate=now(),foperator= :userid "
            sql = sql + " where fholidayid= :fholidayid "
            db.execute(text(sql), {
                "fholidayname": holiday.fholidayname,
                "fbegdate": holiday.fbegdate,
                "fenddate": holiday.fenddate,
                "ftype": holiday.ftype,
                "userId": user_id,
                "fholidayid": holiday.fholidayid,
            })
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@data_holiday_router.post("/delete/{fholidayid}", summary="节假日删除接口")
def delete_holiday(fholidayid: int = Path(description="节假日id"), db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "delete from a_ts_holiday where fholidayid= :fholidayid "
            db.execute(text(sql), {
                "fholidayid": fholidayid,
            })
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
